#!/usr/bin/env python
# -*- encoding: utf-8 -*-
'''
@File    :   level_rate_config.py    
@Contact :   pengwei.sun@aihuishou.com
@License :   (C)Copyright aihuishou

@Modify Time      @Author       @Version    @Desciption
------------      -----------   --------    -----------
2021-01-10 16:48   pengwei.sun      1.0         None
'''

import datetime
import pandas as pd
import numpy as np
from src.utils.config import logger
from src.utils.db_processor import mysql_prediction_processor,presto_processor
from src.mobile.levelrate.config_utils import level_rank_df,product_level_df
import time


"""
取数逻辑 ：
按天汇总skuleve的价格：取平均数
 --b:筛选出具有S,A,B等级，并且等级数存在数大于1的sku
"""
SETTLE_DATA = """
select a.secondary_level_template_id as property_level_template_id,CONCAT(a.secondary_level_template_id,'_',a.product_brand_id) as property_template_brand,a.mapping_product_sku_id as product_sku_id,CONCAT(a.secondary_level_template_id,'_',a.product_brand_id,'_',a.mapping_product_sku_id) as product_sku,
a.mapping_product_level_id as product_level_id,a.mapping_product_level_name as product_level_name,substring(a.mapping_product_level_name,1,1) as level_sub,
avg(a.real_sell_price) as price,
COUNT(*) AS sale_num,
count(distinct a.shop_out_date) as cnt_date
 from product_price_info_ljc  a
inner join (
	select secondary_level_template_id,product_id,mapping_product_sku_id,
	count(distinct mapping_product_level_id) AS cnt,
	count(distinct product_level_id_2) AS cnt1
	from
	(
	select distinct secondary_level_template_id,product_id,mapping_product_sku_id ,mapping_product_level_name ,
	mapping_product_level_id,
	case when substring(mapping_product_level_name,1,1) in ('S','A','B','C','D') 
	THEN mapping_product_level_id ELSE null end product_level_id_2 
	 from product_price_info_ljc 
	where shop_out_date between DATE_FORMAT(date_sub(curdate(),interval 7 day),'%Y%m%d')  and  DATE_FORMAT(date_sub(curdate(),interval 0 day),'%Y%m%d') and product_category_id=1
	and  product_source_id in (101,103)
    and mapping_product_level_name is not null
    and secondary_level_template_id>0
     and (sale_out_cnt is  null OR sale_out_cnt<=1)
	) a
	GROUP BY 1,2
	having  count(distinct product_level_id_2)>0 and count(distinct mapping_product_level_id)>=2
)b
on  a.product_id=b.product_id and a.mapping_product_sku_id=b.mapping_product_sku_id

where a.shop_out_date between DATE_FORMAT(date_sub(curdate(),interval 7 day),'%Y%m%d')  and  DATE_FORMAT(date_sub(curdate(),interval 0 day),'%Y%m%d') and a.product_category_id=1  and mapping_product_level_id>0
and  a.product_source_id in (101,103)
 and (a.sale_out_cnt is  null OR a.sale_out_cnt<=1)
and a.mapping_product_level_name is not null
and a.secondary_level_template_id>0
GROUP BY 1,2,3,4,5,6
order by 1,2,3,4
"""

SETTLE_DATA_2 = """
select a.secondary_level_template_id as property_level_template_id,CONCAT(a.secondary_level_template_id,'_',a.product_brand_id) as property_template_brand,a.mapping_product_sku_id as product_sku_id,CONCAT(a.secondary_level_template_id,'_',a.product_brand_id,'_',a.mapping_product_sku_id) as product_sku,
a.mapping_product_level_id as product_level_id,a.mapping_product_level_name as product_level_name,substring(a.mapping_product_level_name,1,1) as level_sub,
avg(a.real_sell_price) as price,
COUNT(*) AS sale_num,
count(distinct a.shop_out_date) as cnt_date
 from product_price_info_ljc  a
inner join (
	select secondary_level_template_id,product_id,mapping_product_sku_id,
	count(distinct mapping_product_level_id) AS cnt,
	count(distinct product_level_id_2) AS cnt1
	from
	(
	select distinct secondary_level_template_id,product_id,mapping_product_sku_id ,mapping_product_level_name ,
	mapping_product_level_id,
	case when substring(mapping_product_level_name,1,1) in ('S','A','B','C','D') 
	THEN mapping_product_level_id ELSE null end product_level_id_2 
	 from product_price_info_ljc 
	where  shop_out_date between DATE_FORMAT(date_sub(curdate(),interval 14 day),'%Y%m%d')  and DATE_FORMAT(date_sub(curdate(),interval 8 day),'%Y%m%d')  
	and product_category_id=1  and mapping_product_level_id>0
	and  product_source_id in (101,103)
    and mapping_product_level_name is not null
    and secondary_level_template_id>0
     and (sale_out_cnt is  null OR sale_out_cnt<=1)
	) a
	GROUP BY 1,2
	having  count(distinct product_level_id_2)>0 and count(distinct mapping_product_level_id)>=2
)b
on  a.product_id=b.product_id and a.mapping_product_sku_id=b.mapping_product_sku_id

where a.shop_out_date between DATE_FORMAT(date_sub(curdate(),interval 14 day),'%Y%m%d')  and DATE_FORMAT(date_sub(curdate(),interval 8 day),'%Y%m%d')  
 and a.product_category_id=1
and  a.product_source_id in (101,103)
 and (a.sale_out_cnt is  null OR a.sale_out_cnt<=1)
and a.mapping_product_level_name is not null
and a.secondary_level_template_id>0
GROUP BY 1,2,3,4,5,6
order by 1,2,3,4
"""

SETTLE_DATA_3 = """
select a.secondary_level_template_id as property_level_template_id,CONCAT(a.secondary_level_template_id,'_',a.product_brand_id) as property_template_brand,a.mapping_product_sku_id as product_sku_id,CONCAT(a.secondary_level_template_id,'_',a.product_brand_id,'_',a.mapping_product_sku_id) as product_sku,
a.mapping_product_level_id as product_level_id,a.mapping_product_level_name as product_level_name,substring(a.mapping_product_level_name,1,1) as level_sub,
avg(a.real_sell_price) as price,
COUNT(*) AS sale_num,
count(distinct a.shop_out_date) as cnt_date
 from product_price_info_ljc  a
inner join (
	select secondary_level_template_id,product_id,mapping_product_sku_id,
	count(distinct mapping_product_level_id) AS cnt,
	count(distinct product_level_id_2) AS cnt1
	from
	(
	select distinct secondary_level_template_id,product_id,mapping_product_sku_id ,mapping_product_level_name ,
	mapping_product_level_id,
	case when substring(mapping_product_level_name,1,1) in ('S','A','B','C','D') 
	THEN mapping_product_level_id ELSE null end product_level_id_2 
	 from product_price_info_ljc 
	where shop_out_date between DATE_FORMAT(date_sub(curdate(),interval 21 day),'%Y%m%d')  and DATE_FORMAT(date_sub(curdate(),interval 15 day),'%Y%m%d')
	  and product_category_id=1  and mapping_product_level_id>0
	and  product_source_id in (101,103)
    and mapping_product_level_name is not null
    and secondary_level_template_id>0
    and (sale_out_cnt is  null OR sale_out_cnt<=1)
	) a
	GROUP BY 1,2
	having  count(distinct product_level_id_2)>0 and count(distinct mapping_product_level_id)>=2
)b
on  a.product_id=b.product_id and a.mapping_product_sku_id=b.mapping_product_sku_id

where  a.shop_out_date between DATE_FORMAT(date_sub(curdate(),interval 21 day),'%Y%m%d')  and DATE_FORMAT(date_sub(curdate(),interval 15 day),'%Y%m%d') 
 and a.product_category_id=1 
and  a.product_source_id in (101,103)
 and (a.sale_out_cnt is  null OR a.sale_out_cnt<=1)
and a.mapping_product_level_name is not null
and a.secondary_level_template_id>0
GROUP BY 1,2,3,4,5,6
order by 1,2,3,4
"""

#--and a.product_id=32291 S,A,B基准价格
BASE_PRICE_DATA = """
SELECT level_name,price as base_price FROM c_level_base_price
"""

product_ids=(31767, 39143, 37600, 34097, 23906, 39004, 35515, 34688, 39025, 36687, 36248, 33498, 32850, 39165, 37524, 35622,
35164, 38972, 36247, 23866, 36045, 33009, 39177, 37703, 34402, 34053, 37109, 37557, 35557, 34352, 39064, 36300, 37598, 34694, 34086, 27551,
 35941, 35884, 36047, 36046, 26422, 27784, 37330, 34397, 33012, 42861, 35623, 36439, 28421, 30175, 22706, 37680, 35480, 37051, 35963, 20692,
  35100, 37650, 34748, 17425, 38297, 27877, 34464, 29116, 34403, 37512, 24390, 22441, 17527, 36246, 27822, 36939, 37589, 28760, 35106,
  38394, 37217, 35700, 34687, 35290, 29637, 28121, 2246,2247,17455,17457,17460,17458,17459,20079,34701,43512,43513,43511,43510,
  39142, 39087, 38660, 38564, 38257, 37677, 37676, 37664, 37627, 37604, 37519, 37376,
  37094, 37019, 36985, 36805, 36744, 36708, 36510, 36493, 36249, 36095, 36044, 35585, 35584, 35547, 35246, 34948, 34809, 34756, 34755,
  34754, 34753, 34708, 34576, 34575, 34504, 34401, 34324, 34286, 34164, 33468, 33281, 32955, 32835, 32588, 32461, 32460, 32292, 32291,
  32290, 32068, 32066, 32050, 31945, 31008, 30173, 30044, 29705, 29693, 29607, 29291, 29261, 29260, 29115, 29023, 29002, 29000, 28999,
  28968, 28829, 28612, 28495, 28168, 27860, 27859, 27782, 27781, 27640, 27639, 27637, 27627, 27550, 27512, 27344, 27301, 27280, 27253,
  27036, 26959, 26913, 26912, 26707, 26613, 26536, 26512, 26491, 26467, 26464, 26431, 26428, 26381, 26170, 26102, 26099, 26078, 25998,
  25950, 25827, 25820, 25806, 25731, 25680, 25679, 25677, 25676, 25519, 25240, 25170, 25097, 24799, 24348, 24347, 24240, 24217, 23819,
  23788, 23756, 23668, 23640, 23423, 23422, 23049, 20421, 20122, 19664, 17895, 17752, 17726, 17462, 17461, 15080,
  9478, 15080, 17252, 17338, 17507, 17578, 17887, 17895, 17896, 19664, 19665, 20122, 20421, 22187, 22441, 22706, 23049, 23077, 23125,
   23534, 23587, 23614, 23640, 23668, 23760, 23788, 23814, 23819, 23862, 23866, 24217, 24240, 24390, 24992, 25231, 25432, 25519, 25641,
    25682, 25820, 25985, 26062, 26129, 26165, 26167, 26170, 26422, 26490, 26491, 26493, 26513, 26662, 27035, 27036, 27541, 28760, 29348,
    36744, 29260, 21885, 23841, 24552, 26808, 27253, 36246, 32955, 17698, 26098, 34576, 26912, 32588, 35884, 30173, 28495, 20499, 25616,
    25765, 34756, 35762, 27344, 34754, 20751, 21769, 22117, 32827, 34376, 32835, 27550, 36633, 23375, 25400, 25422, 26078, 26536, 27512,
    27782, 29637, 27860, 27781, 34286, 28614, 26431, 19657, 23968, 26671, 29261, 23756, 28168, 34053, 24348, 32244, 26613, 28829, 27980,
    26197, 29003, 32089, 34755, 27301, 29117, 35290, 32890, 27600, 34948, 25232, 19534, 26913, 30175, 17304, 19343, 23865, 24239, 29116,
    31008, 32066, 35547, 36784, 24347, 34590, 32460, 32461, 27549, 27627, 26464, 25097, 34575, 27280, 33280, 17616, 17967, 25401, 27859,
    28779, 29693, 36510, 26959, 33416, 29118, 28612, 27764, 20173, 20174, 25239, 25731, 26099, 36249, 26467, 26492, 29263, 27602, 27110,
    28502, 25806, 29443, 32068, 34164, 34753, 26102, 29291, 36628, 17795, 21036, 24993, 29836, 35164, 39142, 29222, 26428, 27822, 35963,
    36493, 20204, 24232, 32581, 34504, 36804, 34687, 25950, 26958, 32019, 24799, 25170, 26476, 27877, 37018, 37205, 34686, 30044, 34096,
    26707, 26266, 27995, 33009, 38676, 37066, 29023, 33415, 36610, 20606, 20742, 23346, 25240, 29705, 32850, 35271, 35163, 34574, 37519,
    35564, 28480, 17657, 23257, 24349, 25998, 27643, 28121, 32022, 35044, 36687, 33194, 36394, 37376, 37676, 47987, 25676, 35100, 24996,
    27861, 28999, 29208, 31007, 29246, 30174, 33007, 27170, 32884, 35585, 31445, 34748, 17793, 25677, 28253, 34808, 27646, 23906, 34809,
    27006, 33436, 34054, 35006, 14990, 17259, 17912, 20692, 23460, 23619, 23797, 24995, 25948, 26911, 26927, 28443, 36708, 38660, 17746,
    20144, 32716, 35322, 39087, 38600, 35584, 27908, 29666, 35197, 26512, 34324, 29002, 37077, 37677, 38257, 39001, 29777, 36749, 27551,
    29807, 33498, 37600, 25303, 27893, 28968, 38564, 31097, 20658, 22440, 27270, 32988, 35527, 39141, 37589, 17669, 26780, 30384,
    35349, 24966, 35106, 34086, 23535, 35246, 33468, 34287, 33281, 34734, 34097, 29115, 17325, 31945, 36250, 28525, 36805, 14972,
    29706, 29937, 36291, 27190, 36101, 17489, 27297, 29366, 32528, 36093, 36398, 17337, 20603, 23758, 29500, 27783, 35830, 17488,
    21581, 30602, 37687, 29607, 27793, 35809, 23891, 34795, 17425, 18030, 23281, 33363, 34401, 34709, 27882, 27011, 26380, 17886,
    22115, 36789, 17521, 17903, 32472, 37019, 38696, 39143, 32972, 36248, 35623, 34598, 34448, 37627, 17321, 17345, 17493, 17614,
    17712, 17857, 17869, 36985, 23776, 25752, 27334, 29413, 36462, 29845, 34431, 17579, 20618, 33012, 29534, 37487, 34397, 37330,
    25853, 17647, 17988, 29000, 26809, 34403, 30985, 27756, 35451, 17615, 24497, 34733, 25040, 37377, 17610, 23289, 23613, 34365,
    35582, 17611, 28466, 35008, 34708, 37596, 37650, 32055, 37604, 36677, 30385, 36939, 19533, 42701, 26001, 34896, 35941, 37664, 38584,
    30423, 34730, 9208, 17320, 23740, 26899, 34443, 26783, 35005, 37679, 20092, 32634, 32821, 37535, 29135, 42519, 37051, 38884, 42595,
    27778, 32632, 36692, 34526, 32958, 25521, 38250, 34402, 39165, 24341, 26716, 27405, 31440, 17486, 25717, 28684, 33425, 20143, 27693,
    43241, 42705, 34405, 23286, 17527, 17686, 31567, 31550, 31767, 42861, 34897, 43692, 27372, 27784, 35671, 36494, 38630, 39236, 32872,
    34464, 37094, 17787, 35007, 37680, 27491, 34688, 43220, 20418, 28625, 36300, 36352, 10025, 17494, 17713, 36095, 28566, 26027, 17733, 27988,
    35699, 15054, 17875, 23764, 26381, 28478, 32511, 37581,66428,
    38959, 43695, 54816, 37656, 37255, 35833, 43408, 23820, 43693, 76793, 20210, 55383, 42879, 42880, 42860, 57425, 52555, 57426, 43349, 32776, 35181, 52548, 52379, 58585, 43694, 38329,
    99441,96407,99452,96404,97281,94085,68759,97279,99440,90534,62752,64653,96320,90590,25040,76437,91803,58513,
    111413,37330,111365,115120,114989,112846,113325,110293,112285,112648,111336,109022,110593,110295,110288,116018,115792,115287,112856,115658,110125,110590,112387,115118,115816,115507,109096,113518,115508,106485,109002,115818,99442,112897,110289,109352,110169,111445,112893,108928,115648,109424,110840)
#倒数第二行为上次为冷门现在变为热门数据的型号
class TemplateLevelRate:
    def __init__(self,sql):

        # sql = SETTLE_DATA
        #获取型号对应的skulevel聚合后的数据
        if sql is None:
            self.query_sql=SETTLE_DATA
        else:
            self.query_sql=sql

        self.data = mysql_prediction_processor.load_sql(self.query_sql)
        self.data = self.data.loc[self.data.product_level_id > 0]
        #基础等级价格
        self.base_price_df = mysql_prediction_processor.load_sql(BASE_PRICE_DATA)
        self.level_rank_df=level_rank_df

        self.product_level_df =product_level_df

    def fun1(self):
        #根据售卖数量和等级计算每个等级得分，以便后续sku 筛选:高等级的等级id较小，所以采取1000-等级id，使得，相同数量的情况下，高等级的优先选择
        self.data['score']=1000-self.data.product_level_id+self.data.sale_num*1000
        #首先筛选出sku中 有S,A,B等级数据
        data_sku_base_level_df=self.data.loc[self.data.level_sub.isin(['S','A','B','C','D'])]

        # a= data_sku_base_level_df.groupby(by=['product_id','product_sku_id'], as_index=False)['score'].max()
        # index= data_sku_base_level_df.groupby(by=['product_id','product_sku_id'])['score'].idxmax()

        #找出数据中score得分最高的level
        base_level=data_sku_base_level_df.loc[data_sku_base_level_df.sort_values(['product_sku','score']).drop_duplicates('product_sku',keep='last').index]

        base_level.rename(columns={'price':'price_base','score':'score_base','sale_num':'base_sale_num'},inplace=True)

        # base_level
        #统计出满足条件的型号下 sku的数量
        base_product_sku_size=data_sku_base_level_df[['property_template_brand','product_sku_id']].groupby('property_template_brand').agg({'product_sku_id': pd.Series.nunique}).reset_index()
        base_product_sku_size.rename(columns={'product_sku_id':'product_sku_num'},inplace=True)


        #给筛选出的基础数据拼接上其对应的基础价格
        base_level=base_level.merge(self.base_price_df,left_on=['product_level_name'],right_on=['level_name'])
        base_base_level_sale_num=base_level.groupby(by=['property_template_brand'])['base_sale_num'].agg({'base_level_sale_sum':'sum'})
        base_level=base_level.merge(base_base_level_sale_num,on='property_template_brand')
        base_level['sku_weight']=base_level.base_sale_num/base_level.base_level_sale_sum

        # 给数据拼接上他们各自的基础信息
        df_all = self.data.merge(base_level[['product_sku', 'price_base', 'score_base', 'base_price']], on=['product_sku'])

        base_product_sale_num=df_all.groupby('property_template_brand')['sale_num'].agg({'product_sale_sum':'sum' }).reset_index()
        base_product_info=base_product_sku_size.merge(base_product_sale_num,left_on=['property_template_brand'],right_on=['property_template_brand'])
        return base_level,base_product_info

    def fun2(self,base_level,base_product_info):
        t1 = time.time()
        # 给数据拼接上他们各自的基础信息
        df_all = self.data.merge(base_level[['product_sku', 'price_base', 'score_base', 'base_price','sku_weight','base_level_sale_sum','base_sale_num']],
                                 on=['product_sku'])

        #计算等级比率，并还原成基础价格
        df_all['level_rate']=df_all.price/df_all.price_base
        df_all['to_base_price']=df_all.base_price*df_all.level_rate
        df_all = df_all.loc[df_all.product_level_id > 0]
        df_all['product_level_id'] = df_all['product_level_id'].astype(int)
        #型号维度的汇总
        df_all['product_level_id_name']=df_all['property_template_brand'].astype(str)+'_'+df_all['product_level_id'].astype(str)+'_'+df_all['product_level_name']

        #求出汇总后的数据的，平均价格，平均售卖数量等信息
        result1=df_all.groupby(by='product_level_id_name')['to_base_price'].agg({'price_mean':'mean','price_max':'max','price_min':'min','price_media':'median'}).reset_index()#这里
        #型号下 各个等级的 售卖数量相关信息
        cnt_res=df_all.groupby(by='product_level_id_name')['sale_num'].agg({'sale_sum':'sum','sku_cnt':'count'}).reset_index()#这里

        df_all=df_all.merge(cnt_res,on='product_level_id_name')

        #等级价格汇总时 ，是以此等级售卖出的数量占此等级的比例为权重，进行加权计算
        df_all['level_weight']=df_all['sale_num']/df_all['sale_sum']
        df_all['sku_base_level_weight']=df_all['base_sale_num']/df_all['base_level_sale_sum']
        df_all['zh_level_weight']=df_all['sku_base_level_weight']*df_all['level_weight']
        # df_all['zh_level_weight'](by='product_level_id_name').agg({'price_mean':'sum'})

        weight_res = df_all.groupby(by='product_level_id_name')['zh_level_weight'].agg(
            {'weight_sum': 'sum'}).reset_index()  # 这里


        df_all=df_all.merge(weight_res[['product_level_id_name','weight_sum']],on=['product_level_id_name'])
        df_all['weight']=df_all['zh_level_weight']/df_all.weight_sum

        df_all['to_base_price_weight']=df_all['weight']*df_all['to_base_price']
        result=df_all.groupby(by='product_level_id_name')['to_base_price_weight'].agg({'price_mean':'sum','price_max':'sum','price_min':'sum','price_media':'sum'}).reset_index()#这里

        # resulttmp=result.merge(result1,on='product_level_id_name')

        # result=result.sort_values('price_media',ascending=False).reset_index()
        result['property_template_id']=result['product_level_id_name'].str.split('_',expand=True)[0].astype(np.int64)
        result['level_id']=result['product_level_id_name'].str.split('_',expand=True)[2].astype(np.int64)
        result['brand_id']=result['product_level_id_name'].str.split('_',expand=True)[1].astype(np.int64)
        result['property_template_brand'] = result['property_template_id'].astype(str) + '_' + result[
            'brand_id'].astype(str)

        result=result.merge(base_product_info,how='inner',left_on=['property_template_brand'],right_on=['property_template_brand'])
        result=result.merge(self.level_rank_df,left_on=['level_id'],right_on=['product_level_id'])
        result.loc[result['brand_id'].isin([52]), 'product_level_order_rank'] = result.loc[
            result['brand_id'].isin([52]), 'product_level_order_rank_52']
        result_t=result.merge(cnt_res,on='product_level_id_name')

        #求出汇总后的等级比率和sku占比
        result_t['rate']=result_t.price_media/10000

        result_t['sku_rate']=result_t.sku_cnt/result_t.product_sku_num



        result_t=result_t.sort_values(by=['product_level_order_rank','product_level_id'],ascending=[True,True])
        result_t['rate_f']=result_t['rate']
        result_t=result_t.reset_index()
        # result_t_tmp=result_t
        # size=result_t.shape[0]
        resDf = pd.DataFrame(columns=result_t.columns.tolist())
        grouped = result_t.groupby('property_template_brand')
        del result_t
        for name, group in grouped:

            group=group.sort_values(by=['product_level_order_rank','product_level_id'],ascending=[True,True])
            group.reset_index(drop=True, inplace=True)
            size = group.shape[0]
            result_t = group
            if size <= 1:
                resDf = resDf.append(result_t.copy())
                continue
            logger.info('property_template_brand:{}'.format(group.loc[0,'property_template_brand']))
            # if group.loc[0,'property_template_brand']=='36_24':
            #     print('dsds')

            for index in range(size):

                if index==0 :
                    next=0
                    if result_t.loc[index, 'sku_rate']>0.3:
                        continue
                    for next in range(1,size,1):
                        if next>=size:
                            continue
                        if result_t.loc[next, 'sku_rate']>0.3:
                            break

                    if (next<size) and ((result_t.loc[index, 'rate_f']-result_t.loc[next, 'rate_f'])/(next-index)>0.10):
                        # for i in range(next-1,index,-1):
                        result_t.loc[index, 'rate_f']=result_t.loc[next, 'rate_f']+0.1*(next-index)



                    # if result_t.loc[index,'rate_f']>1.2:
                        # result_t.loc[index, 'rate_f']=1.2
                    continue

                # if index==size-1:
                #     break
                if (result_t.loc[index,'product_level_id_name'].find('S')>0 \
                    or result_t.loc[index,'product_level_id_name'].find('A')>0 \
                    or result_t.loc[index, 'product_level_id_name'].find('B') > 0):

                    if result_t.loc[index, 'rate_f']-result_t.loc[index-1,'rate_f']>=0 and result_t.loc[index, 'sku_rate']<0.3:
                        result_t.loc[index, 'rate_f'] = result_t.loc[index-1, 'rate_f']-0.01
                    if result_t.loc[index, 'rate_f']-result_t.loc[index-1,'rate_f']>=0 and result_t.loc[index, 'sku_rate']>=0.3 :
                        rate_flag=result_t.loc[index - 1, 'rate_f']
                        flag_index=index - 1
                        x = index - 1
                        for x in range(index-1, 0, -1):
                            if result_t.loc[x, 'sku_rate']>=0.3:
                                rate_flag=result_t.loc[x, 'rate_f']
                                flag_index=x
                                break
                        if index-x>1 and rate_flag-result_t.loc[index, 'rate_f']>0:
                            rate_bulk=(rate_flag - result_t.loc[index, 'rate_f'])/(index-x)
                            for bulk in range(flag_index+1,index-1,1):
                                result_t.loc[bulk, 'rate_f'] = result_t.loc[bulk + 1, 'rate_f'] - rate_bulk
                    continue
                if (result_t.loc[index,'product_level_id_name'].find('C')>0 \
                        or result_t.loc[index, 'product_level_id_name'].find('D') > 0 \
                        or result_t.loc[index, 'product_level_id_name'].find('E') > 0 \
                        or result_t.loc[index, 'product_level_id_name'].find('F') > 0 \
                        or result_t.loc[index, 'product_level_id_name'].find('G') > 0 \
                        or result_t.loc[index, 'product_level_id_name'].find('H') > 0 \
                        or result_t.loc[index, 'product_level_id_name'].find('I') > 0 \
                        or result_t.loc[index, 'product_level_id_name'].find('J') > 0
                        or result_t.loc[index, 'product_level_id_name'].find('K') > 0):

                    if result_t.loc[index, 'rate_f']-result_t.loc[index-1,'rate_f']>=0 and result_t.loc[index, 'sku_rate']<0.2:
                        result_t.loc[index, 'rate_f'] = result_t.loc[index-1, 'rate_f']-0.005
                    if result_t.loc[index, 'rate_f']-result_t.loc[index-1,'rate_f']>=0 and result_t.loc[index, 'sku_rate']>=0.2 :
                        rate_flag=result_t.loc[index - 1, 'rate_f']
                        flag_index=index - 1
                        x=index-1
                        for x in range(index-1, 0, -1):
                            if result_t.loc[x, 'sku_rate']>=0.2:
                                rate_flag=result_t.loc[x, 'rate_f']
                                flag_index=x
                                break
                        if index-x>1 and rate_flag-result_t.loc[index, 'rate_f']>0:
                            rate_bulk=(rate_flag - result_t.loc[index, 'rate_f'])/(index-x)
                            for bulk in range(flag_index+1,index-1,1):
                                result_t.loc[bulk, 'rate_f'] = result_t.loc[bulk + 1, 'rate_f'] - rate_bulk
                    continue
            resDf = resDf.append(result_t.copy())

        resDf['level_id']=resDf.level_id.astype(np.int64)
        product_level_df=self.product_level_df.merge(resDf,how='left',left_on=['template_brand','level_id'],right_on=['property_template_brand','level_id'])
        product_level_df.drop(['product_level_order_rank', 'product_level_order_rank_52'], axis=1, inplace=True)
        product_level_df=product_level_df.merge(self.level_rank_df,left_on=['level_id'],right_on=['product_level_id'])
        product_level_df.loc[product_level_df['product_brand_id'].isin([52]), 'product_level_order_rank'] = product_level_df.loc[
            product_level_df['product_brand_id'].isin([52]), 'product_level_order_rank_52']
        product_level_df=product_level_df.sort_values(['level_template_id','product_level_order_rank'],ascending=True)

        logger.info('template_level_2_rate_v1 process fun2 Data use time @{}'.format(time.time() - t1))
        return product_level_df

def fun3(process_df,rate_f='mean_rate',w='w_mean'):
    t1 = time.time()

    process_df=process_df.reset_index()
    process_df[rate_f]=process_df[rate_f].fillna(-1)
    resDf = pd.DataFrame(columns=process_df.columns.tolist())

    grouped = process_df.groupby('template_brand')
    for name, group in grouped:
        size=group.shape[0]
        group = group.sort_values(by=['product_level_order_rank', 'product_level_id'], ascending=[True, True])
        group.reset_index(drop=True, inplace=True)
        if size <= 1:
            resDf = resDf.append(group.copy())
            continue
        if group.loc[group.template_brand.isin(['166_7', '172_15', '172_365', '248_16', '283_460', '322_1', '322_10', '322_103', '322_11', '322_12', '322_14', '322_15', '322_16', '322_17'])].shape[0]>0:
            print(group.loc[0,'template_brand'])
        for i in range(size):
            if group.loc[i,rate_f]==-1:
                if i==0:
                    next = 0
                    for next in range(i+1,size,1):
                        if group.loc[next, rate_f]>0:
                            break
                    if next==(size-1) and group.loc[next, rate_f]==-1 and i==0:
                        group.loc[i, rate_f]=1
                    else:
                        group.loc[i, rate_f]=group.loc[next, rate_f]+next*0.03
                    continue
                if i>0:
                    next=size
                    for next in range(i+1,size,1):
                        if group.loc[next, rate_f]>0 and group.loc[next, w]>=0.5:
                            break
                    if next<size-1:
                        group.loc[i, rate_f]=group.loc[i-1, rate_f]-(group.loc[i-1, rate_f]-group.loc[next, rate_f])/(next-i+1)

                    else:
                        varience=(group.loc[i - 1, rate_f]-0.1)/(next-i+1)
                        group.loc[i, rate_f] = group.loc[i - 1, rate_f]-varience
                    continue
            else:
                continue
        resDf = resDf.append(group.copy())
    logger.info('template_level_2_rate_v1 process fun3 Data use time @{}'.format(time.time() - t1))
    return resDf





def process_product_level_rate(sql):

    model=TemplateLevelRate(sql)
    base_level,base_product_info=model.fun1()
    product_level_df=model.fun2(base_level,base_product_info)
    # product_level_df=model.fun3(product_level_df)
    product_level_df.drop_duplicates(subset=['template_brand','level_id'], keep='first', inplace=True)
    return product_level_df

def process_android_template_level_rate_final():
    product_level_df1=process_product_level_rate(SETTLE_DATA)
    product_level_df2=process_product_level_rate(SETTLE_DATA_2)
    product_level_df3=process_product_level_rate(SETTLE_DATA_3)
    #测试
    # product_level_df1=product_level_df3
    # product_level_df2=product_level_df3

    tmp1=product_level_df1[['template_brand','level_id','sku_rate','rate_f']]
    tmp1.rename(columns={'rate_f':'rate_f1','sku_rate':'sku_rate1'},inplace=True)
    tmp2=product_level_df2[['template_brand','level_id','sku_rate','rate_f']]
    tmp2.rename(columns={'rate_f':'rate_f2','sku_rate':'sku_rate2'},inplace=True)


    tmp3=product_level_df3[['template_brand','level_id','sku_rate','rate_f']]
    tmp3.rename(columns={'rate_f':'rate_f3','sku_rate':'sku_rate3'},inplace=True)

    tmp1=tmp1.merge(tmp2,left_on=['template_brand','level_id'],right_on=['template_brand','level_id'])
    tmp1=tmp1.merge(tmp3,left_on=['template_brand','level_id'],right_on=['template_brand','level_id'])


    tmp1.loc[tmp1.rate_f1>0 ,'w_f1']=0.5
    tmp1.loc[tmp1.rate_f2>0 ,'w_f2']=0.3
    tmp1.loc[tmp1.rate_f3>0 ,'w_f3']=0.2

    tmp1['rate_f1_w']=tmp1.w_f1*tmp1.rate_f1
    tmp1['rate_f2_w']=tmp1.w_f2*tmp1.rate_f2
    tmp1['rate_f3_w']=tmp1.w_f3*tmp1.rate_f3
    tmp1['mean_rate'] = tmp1[['rate_f1_w', 'rate_f2_w', 'rate_f3_w']].sum(axis=1)/tmp1[['w_f1', 'w_f2', 'w_f3']].sum(axis=1)
    tmp1['w_mean'] = tmp1[['w_f1', 'w_f2', 'w_f3']].sum(axis=1)
    tmp1['mean_rate_tmp']=tmp1['mean_rate']
    tmp1 = tmp1.merge(level_rank_df, left_on=['level_id'], right_on=['product_level_id'])
    tmp1['brand_id'] = tmp1['template_brand'].apply(lambda x: x.split('_')[1])
    tmp1.loc[tmp1['brand_id'].isin([52]), 'product_level_order_rank'] = tmp1.loc[ tmp1['brand_id'].isin([52]), 'product_level_order_rank_52']

    result_df=fun3(tmp1)
    result_df['w_mean_template']=result_df['w_mean']
    result_df['template'] = result_df['template_brand'].apply(lambda x:x.split('_')[0])
    result_df['brand'] = result_df['template_brand'].apply(lambda x:x.split('_')[1])
    tmp2_not_hot = get_product_sku_num_fun(result_df)
    not_hot_product = tmp2_not_hot['product_id'].values.tolist()
    result_df.to_csv('/data/sunpengwei/tmp/template_level_rate.csv', encoding='utf-8-sig')
    return result_df,not_hot_product



def get_product_sku_num_fun(template_rate):
    # query_sql = """
    #   select product_brand_name ,product_key,product_name,count(distinct product_sku_key) as cnt
    # from price_prediction_level2_rate_price_brand_ljc price
    # where price.date=date_sub(current_date,interval 0 day)
    # group by 1,2,3
    # """
    # sku_num_df = mysql_prediction_processor.load_sql(query_sql)

    query_sale_num_sql="""
    
     select  a.product_brand_id,a.product_id,a.product_name,a.secondary_level_template_id,count(1) as cnt
         from product_price_info_ljc a 
         inner join warehouse.dim_product_level b
         on a.mapping_product_level_id = b.level_id
        where  a.shop_out_date >=date_sub(current_date ,interval 42 day)
        and a.product_category_id=1  and a.mapping_product_level_id>0
        and (a.sale_out_cnt is  null OR a.sale_out_cnt<=1) and a.product_brand_id!=52 
        GROUP BY 1,2,3,4
        order by count(1) desc
    """
    product_sale_num_df = mysql_prediction_processor.load_sql(query_sale_num_sql)

    # template_rate = pd.read_csv('/data/sunpengwei/tmp/template_level_rate.csv', encoding='utf-8-sig')
    template_rate['w_flag']=0
    template_rate.loc[template_rate.w_mean>0,'w_flag']=1
    template_rate1 = template_rate.groupby(by='template_brand')['w_flag'].agg({"cnt_num":'count',"cnt_notnull":"sum"}).reset_index()
    template_rate1['not_null_rate']=template_rate1['cnt_notnull']/template_rate1['cnt_num']
    template_rate1_not_null_df = template_rate1.loc[template_rate1.not_null_rate>=0.5]
    template_rate1_not_null_df['template'] = template_rate1_not_null_df['template_brand'].apply(lambda x:x.split('_')[0])
    template_rate1_not_null_df['brand'] = template_rate1_not_null_df['template_brand'].apply(lambda x:x.split('_')[1])

    template_rate1_not_null_df['brand'] = template_rate1_not_null_df['brand'].astype(np.int64)
    template_rate1_not_null_df['template'] = template_rate1_not_null_df['template'].astype(np.int64)
    tmp1 = product_sale_num_df.merge(template_rate1_not_null_df,how='left',left_on=['product_brand_id','secondary_level_template_id'],right_on=['brand','template'])
    tmp2 = tmp1.loc[tmp1.brand>0]
    tmp2_hot = tmp2.loc[tmp2.product_id.isin(product_ids)]
    tmp2_not_hot = tmp2.loc[~(tmp2.product_id.isin(product_ids))]
    template_rate = template_rate.merge(template_rate1_not_null_df[['template_brand','brand']],how='left',on=['template_brand'])
    template_rate.to_csv('/data/sunpengwei/tmp/predict_price_level_android_rate.csv', encoding='utf-8-sig')
    sale_rate = template_rate.merge(template_rate1_not_null_df[['template_brand']],on='template_brand')
    query_brand_sql="""
    select distinct product_brand_id as brand_id,product_brand_name as brand_name 
    from dim.dim_product where product_category_id=1
    """
    brand_df =presto_processor.load_sql(query_brand_sql)
    brand_df['brand_id'] = brand_df['brand_id'].astype(np.int64)
    sale_rate['brand_id'] = sale_rate['brand_id'].astype(np.int64)
    sale_rate = sale_rate.merge(brand_df,how='left',on='brand_id')
    sale_rate.to_csv('/data/sunpengwei/tmp/template_rate1_not_null_df.csv', encoding='utf-8-sig')
    tmp2_hot.to_csv('/data/sunpengwei/tmp/product_hot_android_ids.csv', encoding='utf-8-sig')
    tmp1.to_csv('/data/sunpengwei/tmp/product_android_all_ids.csv', encoding='utf-8-sig')
    # print('12')
    return tmp2_not_hot

#测试用
def main():
    result_df,not_hot_product=process_android_template_level_rate_final()
    # df=predict_price_data(result_df)
    # df.to_csv('/data/sunpengwei/tmp/predict_price_level_rate.csv', encoding='utf-8-sig')
    template_rate =pd.read_csv('/data/sunpengwei/tmp/template_level_rate.csv', encoding='utf-8-sig')

    get_product_sku_num_fun(template_rate)
    print('1')

if __name__ == '__main__':
    main()